{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 21,
   "id": "02970a09-ac3b-45c2-9e39-b1aaff69e701",
   "metadata": {},
   "outputs": [],
   "source": [
    "import polars as pl\n",
    "from perspective.widget import PerspectiveWidget"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 26,
   "id": "a2cbb302-930a-4f33-b1ed-dad99e22da2d",
   "metadata": {
    "collapsed": true,
    "jupyter": {
     "outputs_hidden": true
    },
    "scrolled": true
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div><style>\n",
       ".dataframe > thead > tr,\n",
       ".dataframe > tbody > tr {\n",
       "  text-align: right;\n",
       "  white-space: pre-wrap;\n",
       "}\n",
       "</style>\n",
       "<small>shape: (363, 18)</small><table border=\"1\" class=\"dataframe\"><thead><tr><th>序号</th><th>券商</th><th>交易日期</th><th>交易时间</th><th>证券代码</th><th>证券名称</th><th>买卖标志</th><th>成交价格</th><th>成交数量</th><th>成交金额</th><th>手续费</th><th>印花税</th><th>过户费</th><th>其他费</th><th>发生金额</th><th>手续费率</th><th>印花税率</th><th>过户费率</th></tr><tr><td>u32</td><td>str</td><td>date</td><td>str</td><td>str</td><td>str</td><td>str</td><td>f64</td><td>f64</td><td>f64</td><td>f64</td><td>f64</td><td>f64</td><td>f64</td><td>f64</td><td>f64</td><td>f64</td><td>f64</td></tr></thead><tbody><tr><td>1</td><td>&quot;湘财&quot;</td><td>2022-07-11</td><td>&quot;09:33:37&quot;</td><td>&quot;000900&quot;</td><td>&quot;现代投资&quot;</td><td>&quot;买入&quot;</td><td>4.05</td><td>34400.0</td><td>139320.0</td><td>22.29</td><td>0.0</td><td>1.39</td><td>0.0</td><td>-139342.29</td><td>0.00016</td><td>0.0</td><td>0.00001</td></tr><tr><td>2</td><td>&quot;湘财&quot;</td><td>2022-07-11</td><td>&quot;09:34:24&quot;</td><td>&quot;601077&quot;</td><td>&quot;渝农商行&quot;</td><td>&quot;买入&quot;</td><td>3.65</td><td>38300.0</td><td>139795.0</td><td>22.37</td><td>0.0</td><td>1.38</td><td>0.0</td><td>-139818.75</td><td>0.00016</td><td>0.0</td><td>0.00001</td></tr><tr><td>3</td><td>&quot;湘财&quot;</td><td>2022-07-11</td><td>&quot;09:36:30&quot;</td><td>&quot;600894&quot;</td><td>&quot;广日股份&quot;</td><td>&quot;买入&quot;</td><td>6.54</td><td>21400.0</td><td>139956.0</td><td>22.39</td><td>0.0</td><td>1.41</td><td>0.0</td><td>-139979.8</td><td>0.00016</td><td>0.0</td><td>0.00001</td></tr><tr><td>4</td><td>&quot;湘财&quot;</td><td>2022-07-11</td><td>&quot;09:37:25&quot;</td><td>&quot;601992&quot;</td><td>&quot;金隅集团&quot;</td><td>&quot;买入&quot;</td><td>2.59</td><td>54000.0</td><td>139860.0</td><td>22.38</td><td>0.0</td><td>1.42</td><td>0.0</td><td>-139883.8</td><td>0.00016</td><td>0.0</td><td>0.00001</td></tr><tr><td>5</td><td>&quot;湘财&quot;</td><td>2022-07-11</td><td>&quot;09:38:16&quot;</td><td>&quot;002462&quot;</td><td>&quot;嘉事堂&quot;</td><td>&quot;买入&quot;</td><td>13.51</td><td>10400.0</td><td>140504.0</td><td>22.48</td><td>0.0</td><td>1.41</td><td>0.0</td><td>-140526.48</td><td>0.00016</td><td>0.0</td><td>0.00001</td></tr><tr><td>&hellip;</td><td>&hellip;</td><td>&hellip;</td><td>&hellip;</td><td>&hellip;</td><td>&hellip;</td><td>&hellip;</td><td>&hellip;</td><td>&hellip;</td><td>&hellip;</td><td>&hellip;</td><td>&hellip;</td><td>&hellip;</td><td>&hellip;</td><td>&hellip;</td><td>&hellip;</td><td>&hellip;</td><td>&hellip;</td></tr><tr><td>359</td><td>&quot;海通两融&quot;</td><td>2023-10-31</td><td>&quot;09:31:53&quot;</td><td>&quot;002956&quot;</td><td>&quot;西麦食品&quot;</td><td>&quot;卖出&quot;</td><td>14.13</td><td>5000.0</td><td>70650.0</td><td>6.74</td><td>35.35</td><td>0.0</td><td>0.0</td><td>70607.91</td><td>0.000095</td><td>0.0005</td><td>0.0</td></tr><tr><td>360</td><td>&quot;海通两融&quot;</td><td>2023-10-31</td><td>&quot;09:39:57&quot;</td><td>&quot;603214&quot;</td><td>&quot;爱婴室&quot;</td><td>&quot;买入&quot;</td><td>15.84</td><td>3100.0</td><td>49104.0</td><td>5.0</td><td>0.0</td><td>0.51</td><td>0.0</td><td>-49109.51</td><td>0.000102</td><td>0.0</td><td>0.00001</td></tr><tr><td>361</td><td>&quot;海通两融&quot;</td><td>2023-10-31</td><td>&quot;09:40:55&quot;</td><td>&quot;300132&quot;</td><td>&quot;青松股份&quot;</td><td>&quot;买入&quot;</td><td>5.21</td><td>9600.0</td><td>50016.0</td><td>5.0</td><td>0.0</td><td>0.0</td><td>0.0</td><td>-50021.0</td><td>0.0001</td><td>0.0</td><td>0.0</td></tr><tr><td>362</td><td>&quot;海通两融&quot;</td><td>2023-10-31</td><td>&quot;09:43:13&quot;</td><td>&quot;002492&quot;</td><td>&quot;恒基达鑫&quot;</td><td>&quot;买入&quot;</td><td>5.91</td><td>8400.0</td><td>49644.0</td><td>5.0</td><td>0.0</td><td>0.0</td><td>0.0</td><td>-49649.0</td><td>0.000101</td><td>0.0</td><td>0.0</td></tr><tr><td>363</td><td>&quot;海通两融&quot;</td><td>2023-10-31</td><td>&quot;09:44:45&quot;</td><td>&quot;002111&quot;</td><td>&quot;威海广泰&quot;</td><td>&quot;买入&quot;</td><td>9.24</td><td>5400.0</td><td>49896.0</td><td>5.0</td><td>0.0</td><td>0.0</td><td>0.0</td><td>-49901.0</td><td>0.0001</td><td>0.0</td><td>0.0</td></tr></tbody></table></div>"
      ],
      "text/plain": [
       "shape: (363, 18)\n",
       "┌──────┬─────────┬─────────┬─────────┬───┬─────────┬─────────┬────────┬────────┐\n",
       "│ 序号 ┆ 券商    ┆ 交易日  ┆ 交易时  ┆ … ┆ 发生金  ┆ 手续费  ┆ 印花税 ┆ 过户费 │\n",
       "│ ---  ┆ ---     ┆ 期      ┆ 间      ┆   ┆ 额      ┆ 率      ┆ 率     ┆ 率     │\n",
       "│ u32  ┆ str     ┆ ---     ┆ ---     ┆   ┆ ---     ┆ ---     ┆ ---    ┆ ---    │\n",
       "│      ┆         ┆ date    ┆ str     ┆   ┆ f64     ┆ f64     ┆ f64    ┆ f64    │\n",
       "╞══════╪═════════╪═════════╪═════════╪═══╪═════════╪═════════╪════════╪════════╡\n",
       "│ 1    ┆ 湘财    ┆ 2022-07 ┆ 09:33:3 ┆ … ┆ -139342 ┆ 0.00016 ┆ 0.0    ┆ 0.0000 │\n",
       "│      ┆         ┆ -11     ┆ 7       ┆   ┆ .29     ┆         ┆        ┆ 1      │\n",
       "│ 2    ┆ 湘财    ┆ 2022-07 ┆ 09:34:2 ┆ … ┆ -139818 ┆ 0.00016 ┆ 0.0    ┆ 0.0000 │\n",
       "│      ┆         ┆ -11     ┆ 4       ┆   ┆ .75     ┆         ┆        ┆ 1      │\n",
       "│ 3    ┆ 湘财    ┆ 2022-07 ┆ 09:36:3 ┆ … ┆ -139979 ┆ 0.00016 ┆ 0.0    ┆ 0.0000 │\n",
       "│      ┆         ┆ -11     ┆ 0       ┆   ┆ .8      ┆         ┆        ┆ 1      │\n",
       "│ 4    ┆ 湘财    ┆ 2022-07 ┆ 09:37:2 ┆ … ┆ -139883 ┆ 0.00016 ┆ 0.0    ┆ 0.0000 │\n",
       "│      ┆         ┆ -11     ┆ 5       ┆   ┆ .8      ┆         ┆        ┆ 1      │\n",
       "│ 5    ┆ 湘财    ┆ 2022-07 ┆ 09:38:1 ┆ … ┆ -140526 ┆ 0.00016 ┆ 0.0    ┆ 0.0000 │\n",
       "│      ┆         ┆ -11     ┆ 6       ┆   ┆ .48     ┆         ┆        ┆ 1      │\n",
       "│ …    ┆ …       ┆ …       ┆ …       ┆ … ┆ …       ┆ …       ┆ …      ┆ …      │\n",
       "│ 359  ┆ 海通两  ┆ 2023-10 ┆ 09:31:5 ┆ … ┆ 70607.9 ┆ 0.00009 ┆ 0.0005 ┆ 0.0    │\n",
       "│      ┆ 融      ┆ -31     ┆ 3       ┆   ┆ 1       ┆ 5       ┆        ┆        │\n",
       "│ 360  ┆ 海通两  ┆ 2023-10 ┆ 09:39:5 ┆ … ┆ -49109. ┆ 0.00010 ┆ 0.0    ┆ 0.0000 │\n",
       "│      ┆ 融      ┆ -31     ┆ 7       ┆   ┆ 51      ┆ 2       ┆        ┆ 1      │\n",
       "│ 361  ┆ 海通两  ┆ 2023-10 ┆ 09:40:5 ┆ … ┆ -50021. ┆ 0.0001  ┆ 0.0    ┆ 0.0    │\n",
       "│      ┆ 融      ┆ -31     ┆ 5       ┆   ┆ 0       ┆         ┆        ┆        │\n",
       "│ 362  ┆ 海通两  ┆ 2023-10 ┆ 09:43:1 ┆ … ┆ -49649. ┆ 0.00010 ┆ 0.0    ┆ 0.0    │\n",
       "│      ┆ 融      ┆ -31     ┆ 3       ┆   ┆ 0       ┆ 1       ┆        ┆        │\n",
       "│ 363  ┆ 海通两  ┆ 2023-10 ┆ 09:44:4 ┆ … ┆ -49901. ┆ 0.0001  ┆ 0.0    ┆ 0.0    │\n",
       "│      ┆ 融      ┆ -31     ┆ 5       ┆   ┆ 0       ┆         ┆        ┆        │\n",
       "└──────┴─────────┴─────────┴─────────┴───┴─────────┴─────────┴────────┴────────┘"
      ]
     },
     "execution_count": 26,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df=pl.read_parquet(\"stock_trades.parquet\")\n",
    "df=df.sort(\"交易日期\",\"交易时间\",\"证券代码\")\n",
    "df[\"其他费\"].describe()\n",
    "df=df.with_columns(\n",
    "    pl.col(\"交易时间\").cast(pl.String),\n",
    "    手续费率=pl.col(\"手续费\")/pl.col(\"成交金额\"),\n",
    "    印花税率=pl.col(\"印花税\")/pl.col(\"成交金额\"),\n",
    "    过户费率=pl.col(\"过户费\")/pl.col(\"成交金额\"),\n",
    ")\n",
    "df=df.with_row_index(\"序号\",1)\n",
    "df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 29,
   "id": "0aa30e7c-fef0-40ef-af74-ce9f6ee6a076",
   "metadata": {
    "collapsed": true,
    "jupyter": {
     "outputs_hidden": true
    },
    "scrolled": true
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div><style>\n",
       ".dataframe > thead > tr,\n",
       ".dataframe > tbody > tr {\n",
       "  text-align: right;\n",
       "  white-space: pre-wrap;\n",
       "}\n",
       "</style>\n",
       "<small>shape: (2, 2)</small><table border=\"1\" class=\"dataframe\"><thead><tr><th>券商</th><th>count</th></tr><tr><td>str</td><td>u32</td></tr></thead><tbody><tr><td>&quot;海通普通&quot;</td><td>33</td></tr><tr><td>&quot;湘财&quot;</td><td>19</td></tr></tbody></table></div>"
      ],
      "text/plain": [
       "shape: (2, 2)\n",
       "┌──────────┬───────┐\n",
       "│ 券商     ┆ count │\n",
       "│ ---      ┆ ---   │\n",
       "│ str      ┆ u32   │\n",
       "╞══════════╪═══════╡\n",
       "│ 海通普通 ┆ 33    │\n",
       "│ 湘财     ┆ 19    │\n",
       "└──────────┴───────┘"
      ]
     },
     "execution_count": 29,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.filter(pl.col(\"手续费\")<5)['券商'].value_counts()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 30,
   "id": "7cae33bb-e9b5-4b73-9964-39e723ba2681",
   "metadata": {
    "collapsed": true,
    "jupyter": {
     "outputs_hidden": true
    }
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div><style>\n",
       ".dataframe > thead > tr,\n",
       ".dataframe > tbody > tr {\n",
       "  text-align: right;\n",
       "  white-space: pre-wrap;\n",
       "}\n",
       "</style>\n",
       "<small>shape: (53, 18)</small><table border=\"1\" class=\"dataframe\"><thead><tr><th>序号</th><th>券商</th><th>交易日期</th><th>交易时间</th><th>证券代码</th><th>证券名称</th><th>买卖标志</th><th>成交价格</th><th>成交数量</th><th>成交金额</th><th>手续费</th><th>印花税</th><th>过户费</th><th>其他费</th><th>发生金额</th><th>手续费率</th><th>印花税率</th><th>过户费率</th></tr><tr><td>u32</td><td>str</td><td>date</td><td>str</td><td>str</td><td>str</td><td>str</td><td>f64</td><td>f64</td><td>f64</td><td>f64</td><td>f64</td><td>f64</td><td>f64</td><td>f64</td><td>f64</td><td>f64</td><td>f64</td></tr></thead><tbody><tr><td>320</td><td>&quot;海通两融&quot;</td><td>2023-08-22</td><td>&quot;09:34:39&quot;</td><td>&quot;300642&quot;</td><td>&quot;透景生命&quot;</td><td>&quot;卖出&quot;</td><td>18.106</td><td>2500.0</td><td>45266.0</td><td>5.0</td><td>45.26</td><td>0.0</td><td>0.0</td><td>45215.74</td><td>0.00011</td><td>0.001</td><td>0.0</td></tr><tr><td>323</td><td>&quot;海通两融&quot;</td><td>2023-08-22</td><td>&quot;09:39:25&quot;</td><td>&quot;600333&quot;</td><td>&quot;长春燃气&quot;</td><td>&quot;卖出&quot;</td><td>5.04</td><td>3900.0</td><td>19656.0</td><td>5.0</td><td>19.64</td><td>0.23</td><td>0.0</td><td>19631.13</td><td>0.000254</td><td>0.000999</td><td>0.000012</td></tr><tr><td>325</td><td>&quot;海通两融&quot;</td><td>2023-08-22</td><td>&quot;10:03:59&quot;</td><td>&quot;600525&quot;</td><td>&quot;长园集团&quot;</td><td>&quot;买入&quot;</td><td>4.88</td><td>8500.0</td><td>41480.0</td><td>5.0</td><td>0.0</td><td>0.35</td><td>0.0</td><td>-41485.35</td><td>0.000121</td><td>0.0</td><td>0.000008</td></tr><tr><td>327</td><td>&quot;海通两融&quot;</td><td>2023-09-01</td><td>&quot;10:22:36&quot;</td><td>&quot;300889&quot;</td><td>&quot;爱克股份&quot;</td><td>&quot;卖出&quot;</td><td>15.22</td><td>1500.0</td><td>22830.0</td><td>5.0</td><td>11.42</td><td>0.0</td><td>0.0</td><td>22813.58</td><td>0.000219</td><td>0.0005</td><td>0.0</td></tr><tr><td>329</td><td>&quot;海通两融&quot;</td><td>2023-09-01</td><td>&quot;10:23:28&quot;</td><td>&quot;300889&quot;</td><td>&quot;爱克股份&quot;</td><td>&quot;卖出&quot;</td><td>15.21</td><td>1700.0</td><td>25857.0</td><td>5.0</td><td>12.92</td><td>0.0</td><td>0.0</td><td>25839.08</td><td>0.000193</td><td>0.0005</td><td>0.0</td></tr><tr><td>&hellip;</td><td>&hellip;</td><td>&hellip;</td><td>&hellip;</td><td>&hellip;</td><td>&hellip;</td><td>&hellip;</td><td>&hellip;</td><td>&hellip;</td><td>&hellip;</td><td>&hellip;</td><td>&hellip;</td><td>&hellip;</td><td>&hellip;</td><td>&hellip;</td><td>&hellip;</td><td>&hellip;</td><td>&hellip;</td></tr><tr><td>322</td><td>&quot;海通两融&quot;</td><td>2023-08-22</td><td>&quot;09:36:13&quot;</td><td>&quot;603967&quot;</td><td>&quot;中创物流&quot;</td><td>&quot;卖出&quot;</td><td>9.36</td><td>10600.0</td><td>99216.0</td><td>10.91</td><td>99.24</td><td>1.0</td><td>0.0</td><td>99104.85</td><td>0.00011</td><td>0.001</td><td>0.00001</td></tr><tr><td>319</td><td>&quot;海通两融&quot;</td><td>2023-08-09</td><td>&quot;10:12:16&quot;</td><td>&quot;600336&quot;</td><td>&quot;澳柯玛&quot;</td><td>&quot;买入&quot;</td><td>5.72</td><td>17400.0</td><td>99528.0</td><td>10.95</td><td>0.0</td><td>1.0</td><td>0.0</td><td>-99539.95</td><td>0.00011</td><td>0.0</td><td>0.00001</td></tr><tr><td>324</td><td>&quot;海通两融&quot;</td><td>2023-08-22</td><td>&quot;09:58:46&quot;</td><td>&quot;603626&quot;</td><td>&quot;科森科技&quot;</td><td>&quot;买入&quot;</td><td>6.35</td><td>15700.0</td><td>99695.0</td><td>10.97</td><td>0.0</td><td>1.02</td><td>0.0</td><td>-99706.99</td><td>0.00011</td><td>0.0</td><td>0.00001</td></tr><tr><td>316</td><td>&quot;海通两融&quot;</td><td>2023-08-04</td><td>&quot;10:07:37&quot;</td><td>&quot;603967&quot;</td><td>&quot;中创物流&quot;</td><td>&quot;买入&quot;</td><td>9.43</td><td>10600.0</td><td>99958.0</td><td>11.0</td><td>0.0</td><td>1.01</td><td>0.0</td><td>-99970.01</td><td>0.00011</td><td>0.0</td><td>0.00001</td></tr><tr><td>340</td><td>&quot;海通两融&quot;</td><td>2023-09-12</td><td>&quot;14:31:39&quot;</td><td>&quot;603626&quot;</td><td>&quot;科森科技&quot;</td><td>&quot;卖出&quot;</td><td>7.43</td><td>15700.0</td><td>116651.0</td><td>11.13</td><td>58.32</td><td>1.17</td><td>0.0</td><td>116580.38</td><td>0.000095</td><td>0.0005</td><td>0.00001</td></tr></tbody></table></div>"
      ],
      "text/plain": [
       "shape: (53, 18)\n",
       "┌──────┬─────────┬─────────┬─────────┬───┬─────────┬─────────┬────────┬────────┐\n",
       "│ 序号 ┆ 券商    ┆ 交易日  ┆ 交易时  ┆ … ┆ 发生金  ┆ 手续费  ┆ 印花税 ┆ 过户费 │\n",
       "│ ---  ┆ ---     ┆ 期      ┆ 间      ┆   ┆ 额      ┆ 率      ┆ 率     ┆ 率     │\n",
       "│ u32  ┆ str     ┆ ---     ┆ ---     ┆   ┆ ---     ┆ ---     ┆ ---    ┆ ---    │\n",
       "│      ┆         ┆ date    ┆ str     ┆   ┆ f64     ┆ f64     ┆ f64    ┆ f64    │\n",
       "╞══════╪═════════╪═════════╪═════════╪═══╪═════════╪═════════╪════════╪════════╡\n",
       "│ 320  ┆ 海通两  ┆ 2023-08 ┆ 09:34:3 ┆ … ┆ 45215.7 ┆ 0.00011 ┆ 0.001  ┆ 0.0    │\n",
       "│      ┆ 融      ┆ -22     ┆ 9       ┆   ┆ 4       ┆         ┆        ┆        │\n",
       "│ 323  ┆ 海通两  ┆ 2023-08 ┆ 09:39:2 ┆ … ┆ 19631.1 ┆ 0.00025 ┆ 0.0009 ┆ 0.0000 │\n",
       "│      ┆ 融      ┆ -22     ┆ 5       ┆   ┆ 3       ┆ 4       ┆ 99     ┆ 12     │\n",
       "│ 325  ┆ 海通两  ┆ 2023-08 ┆ 10:03:5 ┆ … ┆ -41485. ┆ 0.00012 ┆ 0.0    ┆ 0.0000 │\n",
       "│      ┆ 融      ┆ -22     ┆ 9       ┆   ┆ 35      ┆ 1       ┆        ┆ 08     │\n",
       "│ 327  ┆ 海通两  ┆ 2023-09 ┆ 10:22:3 ┆ … ┆ 22813.5 ┆ 0.00021 ┆ 0.0005 ┆ 0.0    │\n",
       "│      ┆ 融      ┆ -01     ┆ 6       ┆   ┆ 8       ┆ 9       ┆        ┆        │\n",
       "│ 329  ┆ 海通两  ┆ 2023-09 ┆ 10:23:2 ┆ … ┆ 25839.0 ┆ 0.00019 ┆ 0.0005 ┆ 0.0    │\n",
       "│      ┆ 融      ┆ -01     ┆ 8       ┆   ┆ 8       ┆ 3       ┆        ┆        │\n",
       "│ …    ┆ …       ┆ …       ┆ …       ┆ … ┆ …       ┆ …       ┆ …      ┆ …      │\n",
       "│ 322  ┆ 海通两  ┆ 2023-08 ┆ 09:36:1 ┆ … ┆ 99104.8 ┆ 0.00011 ┆ 0.001  ┆ 0.0000 │\n",
       "│      ┆ 融      ┆ -22     ┆ 3       ┆   ┆ 5       ┆         ┆        ┆ 1      │\n",
       "│ 319  ┆ 海通两  ┆ 2023-08 ┆ 10:12:1 ┆ … ┆ -99539. ┆ 0.00011 ┆ 0.0    ┆ 0.0000 │\n",
       "│      ┆ 融      ┆ -09     ┆ 6       ┆   ┆ 95      ┆         ┆        ┆ 1      │\n",
       "│ 324  ┆ 海通两  ┆ 2023-08 ┆ 09:58:4 ┆ … ┆ -99706. ┆ 0.00011 ┆ 0.0    ┆ 0.0000 │\n",
       "│      ┆ 融      ┆ -22     ┆ 6       ┆   ┆ 99      ┆         ┆        ┆ 1      │\n",
       "│ 316  ┆ 海通两  ┆ 2023-08 ┆ 10:07:3 ┆ … ┆ -99970. ┆ 0.00011 ┆ 0.0    ┆ 0.0000 │\n",
       "│      ┆ 融      ┆ -04     ┆ 7       ┆   ┆ 01      ┆         ┆        ┆ 1      │\n",
       "│ 340  ┆ 海通两  ┆ 2023-09 ┆ 14:31:3 ┆ … ┆ 116580. ┆ 0.00009 ┆ 0.0005 ┆ 0.0000 │\n",
       "│      ┆ 融      ┆ -12     ┆ 9       ┆   ┆ 38      ┆ 5       ┆        ┆ 1      │\n",
       "└──────┴─────────┴─────────┴─────────┴───┴─────────┴─────────┴────────┴────────┘"
      ]
     },
     "execution_count": 30,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.filter(pl.col(\"券商\")==\"海通两融\").sort(\"手续费\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 27,
   "id": "4aa351ce-875b-4459-ac92-1696ca1ec5d2",
   "metadata": {
    "collapsed": true,
    "jupyter": {
     "outputs_hidden": true
    }
   },
   "outputs": [
    {
     "data": {
      "application/vnd.jupyter.widget-view+json": {
       "model_id": "ad71523bb909494ea31db563003d3cc7",
       "version_major": 2,
       "version_minor": 0
      },
      "text/plain": [
       "PerspectiveWidget(binding_mode='server', columns=['序号', '券商', '交易日期', '交易时间', '证券代码', '证券名称', '买卖标志', '成交价格', …"
      ]
     },
     "execution_count": 27,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "PerspectiveWidget(df)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 45,
   "id": "5c2637b2-694b-4479-9901-806dc11497e1",
   "metadata": {},
   "outputs": [],
   "source": [
    "d1=df.join(\n",
    "    df.group_by(\"证券代码\",\"证券名称\")\n",
    "    .agg(\n",
    "        结余数量=(pl.when(pl.col(\"买卖标志\")==\"卖出\").then(-pl.col(\"成交数量\")).when(pl.col(\"买卖标志\")==\"买入\").then(pl.col(\"成交数量\")).sum()),\n",
    "    )\n",
    "    .filter(pl.col(\"结余数量\")<0),\n",
    "    on=\"证券代码\",\n",
    "    how=\"anti\",\n",
    ")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 47,
   "id": "c29f0f94-2011-42f2-b54f-894cf04aa7ae",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "datetime.date(2022, 7, 11)"
      ]
     },
     "execution_count": 47,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "start_date=df[\"交易日期\"].min()\n",
    "start_date"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 48,
   "id": "c38b463d-ca67-449b-8914-a762e6a08dd4",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "datetime.date(2023, 10, 31)"
      ]
     },
     "execution_count": 48,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "end_date=df[\"交易日期\"].max()\n",
    "end_date"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 53,
   "id": "69354ff7-09e2-4bb3-8b9e-46e46f06a258",
   "metadata": {
    "collapsed": true,
    "jupyter": {
     "outputs_hidden": true
    },
    "scrolled": true
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div><style>\n",
       ".dataframe > thead > tr,\n",
       ".dataframe > tbody > tr {\n",
       "  text-align: right;\n",
       "  white-space: pre-wrap;\n",
       "}\n",
       "</style>\n",
       "<small>shape: (478, 1)</small><table border=\"1\" class=\"dataframe\"><thead><tr><th>日期</th></tr><tr><td>date</td></tr></thead><tbody><tr><td>2022-07-11</td></tr><tr><td>2022-07-12</td></tr><tr><td>2022-07-13</td></tr><tr><td>2022-07-14</td></tr><tr><td>2022-07-15</td></tr><tr><td>&hellip;</td></tr><tr><td>2023-10-27</td></tr><tr><td>2023-10-28</td></tr><tr><td>2023-10-29</td></tr><tr><td>2023-10-30</td></tr><tr><td>2023-10-31</td></tr></tbody></table></div>"
      ],
      "text/plain": [
       "shape: (478, 1)\n",
       "┌────────────┐\n",
       "│ 日期       │\n",
       "│ ---        │\n",
       "│ date       │\n",
       "╞════════════╡\n",
       "│ 2022-07-11 │\n",
       "│ 2022-07-12 │\n",
       "│ 2022-07-13 │\n",
       "│ 2022-07-14 │\n",
       "│ 2022-07-15 │\n",
       "│ …          │\n",
       "│ 2023-10-27 │\n",
       "│ 2023-10-28 │\n",
       "│ 2023-10-29 │\n",
       "│ 2023-10-30 │\n",
       "│ 2023-10-31 │\n",
       "└────────────┘"
      ]
     },
     "execution_count": 53,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "k1=pl.select(\n",
    "    日期=pl.date_range(start_date,end_date)\n",
    ")\n",
    "k1"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 52,
   "id": "b2c64ab9-b526-43ee-90ad-e59a3534fe24",
   "metadata": {
    "collapsed": true,
    "jupyter": {
     "outputs_hidden": true
    }
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div><style>\n",
       ".dataframe > thead > tr,\n",
       ".dataframe > tbody > tr {\n",
       "  text-align: right;\n",
       "  white-space: pre-wrap;\n",
       "}\n",
       "</style>\n",
       "<small>shape: (149, 1)</small><table border=\"1\" class=\"dataframe\"><thead><tr><th>证券代码</th></tr><tr><td>str</td></tr></thead><tbody><tr><td>&quot;000096&quot;</td></tr><tr><td>&quot;000532&quot;</td></tr><tr><td>&quot;000559&quot;</td></tr><tr><td>&quot;000599&quot;</td></tr><tr><td>&quot;000655&quot;</td></tr><tr><td>&hellip;</td></tr><tr><td>&quot;688299&quot;</td></tr><tr><td>&quot;688321&quot;</td></tr><tr><td>&quot;688360&quot;</td></tr><tr><td>&quot;688393&quot;</td></tr><tr><td>&quot;688660&quot;</td></tr></tbody></table></div>"
      ],
      "text/plain": [
       "shape: (149, 1)\n",
       "┌──────────┐\n",
       "│ 证券代码 │\n",
       "│ ---      │\n",
       "│ str      │\n",
       "╞══════════╡\n",
       "│ 000096   │\n",
       "│ 000532   │\n",
       "│ 000559   │\n",
       "│ 000599   │\n",
       "│ 000655   │\n",
       "│ …        │\n",
       "│ 688299   │\n",
       "│ 688321   │\n",
       "│ 688360   │\n",
       "│ 688393   │\n",
       "│ 688660   │\n",
       "└──────────┘"
      ]
     },
     "execution_count": 52,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "k2=df[\"证券代码\"].unique().sort().to_frame()\n",
    "k2"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 55,
   "id": "2ed901c8-87a1-4986-b898-197dc4d3d032",
   "metadata": {},
   "outputs": [],
   "source": [
    "k=k1.join(k2,how=\"cross\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 63,
   "id": "2d31b468-0730-44c7-9b0e-9be03203988c",
   "metadata": {
    "collapsed": true,
    "jupyter": {
     "outputs_hidden": true
    },
    "scrolled": true
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div><style>\n",
       ".dataframe > thead > tr,\n",
       ".dataframe > tbody > tr {\n",
       "  text-align: right;\n",
       "  white-space: pre-wrap;\n",
       "}\n",
       "</style>\n",
       "<small>shape: (4_489, 19)</small><table border=\"1\" class=\"dataframe\"><thead><tr><th>日期</th><th>证券代码</th><th>序号</th><th>券商</th><th>交易时间</th><th>证券名称</th><th>买卖标志</th><th>成交价格</th><th>成交数量</th><th>成交金额</th><th>手续费</th><th>印花税</th><th>过户费</th><th>其他费</th><th>发生金额</th><th>手续费率</th><th>印花税率</th><th>过户费率</th><th>结余数量</th></tr><tr><td>date</td><td>str</td><td>u32</td><td>str</td><td>str</td><td>str</td><td>str</td><td>f64</td><td>f64</td><td>f64</td><td>f64</td><td>f64</td><td>f64</td><td>f64</td><td>f64</td><td>f64</td><td>f64</td><td>f64</td><td>f64</td></tr></thead><tbody><tr><td>2022-07-11</td><td>&quot;000900&quot;</td><td>1</td><td>&quot;湘财&quot;</td><td>&quot;09:33:37&quot;</td><td>&quot;现代投资&quot;</td><td>&quot;买入&quot;</td><td>4.05</td><td>34400.0</td><td>139320.0</td><td>22.29</td><td>0.0</td><td>1.39</td><td>0.0</td><td>-139342.29</td><td>0.00016</td><td>0.0</td><td>0.00001</td><td>34400.0</td></tr><tr><td>2022-07-11</td><td>&quot;002462&quot;</td><td>5</td><td>&quot;湘财&quot;</td><td>&quot;09:38:16&quot;</td><td>&quot;嘉事堂&quot;</td><td>&quot;买入&quot;</td><td>13.51</td><td>10400.0</td><td>140504.0</td><td>22.48</td><td>0.0</td><td>1.41</td><td>0.0</td><td>-140526.48</td><td>0.00016</td><td>0.0</td><td>0.00001</td><td>10400.0</td></tr><tr><td>2022-07-11</td><td>&quot;600894&quot;</td><td>3</td><td>&quot;湘财&quot;</td><td>&quot;09:36:30&quot;</td><td>&quot;广日股份&quot;</td><td>&quot;买入&quot;</td><td>6.54</td><td>21400.0</td><td>139956.0</td><td>22.39</td><td>0.0</td><td>1.41</td><td>0.0</td><td>-139979.8</td><td>0.00016</td><td>0.0</td><td>0.00001</td><td>21400.0</td></tr><tr><td>2022-07-11</td><td>&quot;601077&quot;</td><td>2</td><td>&quot;湘财&quot;</td><td>&quot;09:34:24&quot;</td><td>&quot;渝农商行&quot;</td><td>&quot;买入&quot;</td><td>3.65</td><td>38300.0</td><td>139795.0</td><td>22.37</td><td>0.0</td><td>1.38</td><td>0.0</td><td>-139818.75</td><td>0.00016</td><td>0.0</td><td>0.00001</td><td>38300.0</td></tr><tr><td>2022-07-11</td><td>&quot;601992&quot;</td><td>4</td><td>&quot;湘财&quot;</td><td>&quot;09:37:25&quot;</td><td>&quot;金隅集团&quot;</td><td>&quot;买入&quot;</td><td>2.59</td><td>54000.0</td><td>139860.0</td><td>22.38</td><td>0.0</td><td>1.42</td><td>0.0</td><td>-139883.8</td><td>0.00016</td><td>0.0</td><td>0.00001</td><td>54000.0</td></tr><tr><td>&hellip;</td><td>&hellip;</td><td>&hellip;</td><td>&hellip;</td><td>&hellip;</td><td>&hellip;</td><td>&hellip;</td><td>&hellip;</td><td>&hellip;</td><td>&hellip;</td><td>&hellip;</td><td>&hellip;</td><td>&hellip;</td><td>&hellip;</td><td>&hellip;</td><td>&hellip;</td><td>&hellip;</td><td>&hellip;</td><td>&hellip;</td></tr><tr><td>2023-10-31</td><td>&quot;300132&quot;</td><td>361</td><td>&quot;海通两融&quot;</td><td>&quot;09:40:55&quot;</td><td>&quot;青松股份&quot;</td><td>&quot;买入&quot;</td><td>5.21</td><td>9600.0</td><td>50016.0</td><td>5.0</td><td>0.0</td><td>0.0</td><td>0.0</td><td>-50021.0</td><td>0.0001</td><td>0.0</td><td>0.0</td><td>9600.0</td></tr><tr><td>2023-10-31</td><td>&quot;300215&quot;</td><td>null</td><td>null</td><td>null</td><td>null</td><td>null</td><td>null</td><td>null</td><td>null</td><td>null</td><td>null</td><td>null</td><td>null</td><td>null</td><td>null</td><td>null</td><td>null</td><td>20000.0</td></tr><tr><td>2023-10-31</td><td>&quot;600525&quot;</td><td>null</td><td>null</td><td>null</td><td>null</td><td>null</td><td>null</td><td>null</td><td>null</td><td>null</td><td>null</td><td>null</td><td>null</td><td>null</td><td>null</td><td>null</td><td>null</td><td>8500.0</td></tr><tr><td>2023-10-31</td><td>&quot;603214&quot;</td><td>360</td><td>&quot;海通两融&quot;</td><td>&quot;09:39:57&quot;</td><td>&quot;爱婴室&quot;</td><td>&quot;买入&quot;</td><td>15.84</td><td>3100.0</td><td>49104.0</td><td>5.0</td><td>0.0</td><td>0.51</td><td>0.0</td><td>-49109.51</td><td>0.000102</td><td>0.0</td><td>0.00001</td><td>3100.0</td></tr><tr><td>2023-10-31</td><td>&quot;688660&quot;</td><td>null</td><td>null</td><td>null</td><td>null</td><td>null</td><td>null</td><td>null</td><td>null</td><td>null</td><td>null</td><td>null</td><td>null</td><td>null</td><td>null</td><td>null</td><td>null</td><td>20200.0</td></tr></tbody></table></div>"
      ],
      "text/plain": [
       "shape: (4_489, 19)\n",
       "┌─────────┬─────────┬──────┬─────────┬───┬─────────┬─────────┬────────┬────────┐\n",
       "│ 日期    ┆ 证券代  ┆ 序号 ┆ 券商    ┆ … ┆ 手续费  ┆ 印花税  ┆ 过户费 ┆ 结余数 │\n",
       "│ ---     ┆ 码      ┆ ---  ┆ ---     ┆   ┆ 率      ┆ 率      ┆ 率     ┆ 量     │\n",
       "│ date    ┆ ---     ┆ u32  ┆ str     ┆   ┆ ---     ┆ ---     ┆ ---    ┆ ---    │\n",
       "│         ┆ str     ┆      ┆         ┆   ┆ f64     ┆ f64     ┆ f64    ┆ f64    │\n",
       "╞═════════╪═════════╪══════╪═════════╪═══╪═════════╪═════════╪════════╪════════╡\n",
       "│ 2022-07 ┆ 000900  ┆ 1    ┆ 湘财    ┆ … ┆ 0.00016 ┆ 0.0     ┆ 0.0000 ┆ 34400. │\n",
       "│ -11     ┆         ┆      ┆         ┆   ┆         ┆         ┆ 1      ┆ 0      │\n",
       "│ 2022-07 ┆ 002462  ┆ 5    ┆ 湘财    ┆ … ┆ 0.00016 ┆ 0.0     ┆ 0.0000 ┆ 10400. │\n",
       "│ -11     ┆         ┆      ┆         ┆   ┆         ┆         ┆ 1      ┆ 0      │\n",
       "│ 2022-07 ┆ 600894  ┆ 3    ┆ 湘财    ┆ … ┆ 0.00016 ┆ 0.0     ┆ 0.0000 ┆ 21400. │\n",
       "│ -11     ┆         ┆      ┆         ┆   ┆         ┆         ┆ 1      ┆ 0      │\n",
       "│ 2022-07 ┆ 601077  ┆ 2    ┆ 湘财    ┆ … ┆ 0.00016 ┆ 0.0     ┆ 0.0000 ┆ 38300. │\n",
       "│ -11     ┆         ┆      ┆         ┆   ┆         ┆         ┆ 1      ┆ 0      │\n",
       "│ 2022-07 ┆ 601992  ┆ 4    ┆ 湘财    ┆ … ┆ 0.00016 ┆ 0.0     ┆ 0.0000 ┆ 54000. │\n",
       "│ -11     ┆         ┆      ┆         ┆   ┆         ┆         ┆ 1      ┆ 0      │\n",
       "│ …       ┆ …       ┆ …    ┆ …       ┆ … ┆ …       ┆ …       ┆ …      ┆ …      │\n",
       "│ 2023-10 ┆ 300132  ┆ 361  ┆ 海通两  ┆ … ┆ 0.0001  ┆ 0.0     ┆ 0.0    ┆ 9600.0 │\n",
       "│ -31     ┆         ┆      ┆ 融      ┆   ┆         ┆         ┆        ┆        │\n",
       "│ 2023-10 ┆ 300215  ┆ null ┆ null    ┆ … ┆ null    ┆ null    ┆ null   ┆ 20000. │\n",
       "│ -31     ┆         ┆      ┆         ┆   ┆         ┆         ┆        ┆ 0      │\n",
       "│ 2023-10 ┆ 600525  ┆ null ┆ null    ┆ … ┆ null    ┆ null    ┆ null   ┆ 8500.0 │\n",
       "│ -31     ┆         ┆      ┆         ┆   ┆         ┆         ┆        ┆        │\n",
       "│ 2023-10 ┆ 603214  ┆ 360  ┆ 海通两  ┆ … ┆ 0.00010 ┆ 0.0     ┆ 0.0000 ┆ 3100.0 │\n",
       "│ -31     ┆         ┆      ┆ 融      ┆   ┆ 2       ┆         ┆ 1      ┆        │\n",
       "│ 2023-10 ┆ 688660  ┆ null ┆ null    ┆ … ┆ null    ┆ null    ┆ null   ┆ 20200. │\n",
       "│ -31     ┆         ┆      ┆         ┆   ┆         ┆         ┆        ┆ 0      │\n",
       "└─────────┴─────────┴──────┴─────────┴───┴─────────┴─────────┴────────┴────────┘"
      ]
     },
     "execution_count": 63,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "d2=k.join(d1,left_on=[\"日期\",\"证券代码\"],right_on=[\"交易日期\",\"证券代码\"],how=\"left\").sort(\"日期\",\"证券代码\").with_columns(\n",
    "    结余数量=(pl.when(pl.col(\"买卖标志\")==\"买入\").then(pl.col(\"成交数量\")).when(pl.col(\"买卖标志\")==\"卖出\").then(-pl.col(\"成交数量\")).otherwise(0)\n",
    "         .cum_sum().over(\"证券代码\")\n",
    "         ),\n",
    ").filter(\n",
    "    pl.col.结余数量>0)\n",
    "d2"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 64,
   "id": "38c426c8-d98e-4e54-9d0e-d5e6d7fc1824",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "application/vnd.jupyter.widget-view+json": {
       "model_id": "418bfb810ee0422cb7ac7660187bfd70",
       "version_major": 2,
       "version_minor": 0
      },
      "text/plain": [
       "PerspectiveWidget(binding_mode='server', columns=['日期', '证券代码', '序号', '券商', '交易时间', '证券名称', '买卖标志', '成交价格', '成…"
      ]
     },
     "execution_count": 64,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "PerspectiveWidget(d2)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 65,
   "id": "118aa5a3-73b3-4f82-9ee6-a757e6b279b5",
   "metadata": {},
   "outputs": [],
   "source": [
    "import tushare as ts"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 66,
   "id": "757de955-c4e6-4542-b482-e55f86217c87",
   "metadata": {},
   "outputs": [],
   "source": [
    "pro = ts.pro_api()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 68,
   "id": "4c0084b5-aca4-4b59-9f38-93216067ad32",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "datetime.date(2022, 7, 11)"
      ]
     },
     "execution_count": 68,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "start_date"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 69,
   "id": "29218db8-aba2-4c0b-a8e3-e1d3003af20b",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "datetime.date(2023, 10, 31)"
      ]
     },
     "execution_count": 69,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "end_date"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 70,
   "id": "5e90cefa-b611-4c98-a054-841d67d6c22b",
   "metadata": {},
   "outputs": [],
   "source": [
    "hp=pro.daily(ts_code=\"002462.SZ\",start_date=\"20220711\",end_date=\"20231031\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 71,
   "id": "8b0e1f9e-0ad0-4470-9752-6246d63bc40a",
   "metadata": {
    "collapsed": true,
    "jupyter": {
     "outputs_hidden": true
    }
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>ts_code</th>\n",
       "      <th>trade_date</th>\n",
       "      <th>open</th>\n",
       "      <th>high</th>\n",
       "      <th>low</th>\n",
       "      <th>close</th>\n",
       "      <th>pre_close</th>\n",
       "      <th>change</th>\n",
       "      <th>pct_chg</th>\n",
       "      <th>vol</th>\n",
       "      <th>amount</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>002462.SZ</td>\n",
       "      <td>20231031</td>\n",
       "      <td>14.75</td>\n",
       "      <td>14.90</td>\n",
       "      <td>14.59</td>\n",
       "      <td>14.70</td>\n",
       "      <td>14.75</td>\n",
       "      <td>-0.05</td>\n",
       "      <td>-0.3390</td>\n",
       "      <td>65859.96</td>\n",
       "      <td>96984.271</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>002462.SZ</td>\n",
       "      <td>20231030</td>\n",
       "      <td>13.88</td>\n",
       "      <td>14.89</td>\n",
       "      <td>13.88</td>\n",
       "      <td>14.75</td>\n",
       "      <td>13.93</td>\n",
       "      <td>0.82</td>\n",
       "      <td>5.8866</td>\n",
       "      <td>123932.16</td>\n",
       "      <td>180119.372</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>002462.SZ</td>\n",
       "      <td>20231027</td>\n",
       "      <td>13.70</td>\n",
       "      <td>13.98</td>\n",
       "      <td>13.51</td>\n",
       "      <td>13.93</td>\n",
       "      <td>13.64</td>\n",
       "      <td>0.29</td>\n",
       "      <td>2.1261</td>\n",
       "      <td>35782.00</td>\n",
       "      <td>49386.168</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>002462.SZ</td>\n",
       "      <td>20231026</td>\n",
       "      <td>13.49</td>\n",
       "      <td>13.68</td>\n",
       "      <td>13.40</td>\n",
       "      <td>13.64</td>\n",
       "      <td>13.62</td>\n",
       "      <td>0.02</td>\n",
       "      <td>0.1468</td>\n",
       "      <td>19215.00</td>\n",
       "      <td>26005.866</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>002462.SZ</td>\n",
       "      <td>20231025</td>\n",
       "      <td>13.65</td>\n",
       "      <td>13.77</td>\n",
       "      <td>13.58</td>\n",
       "      <td>13.62</td>\n",
       "      <td>13.67</td>\n",
       "      <td>-0.05</td>\n",
       "      <td>-0.3658</td>\n",
       "      <td>18484.00</td>\n",
       "      <td>25274.163</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>313</th>\n",
       "      <td>002462.SZ</td>\n",
       "      <td>20220715</td>\n",
       "      <td>13.61</td>\n",
       "      <td>13.66</td>\n",
       "      <td>13.12</td>\n",
       "      <td>13.13</td>\n",
       "      <td>13.59</td>\n",
       "      <td>-0.46</td>\n",
       "      <td>-3.3848</td>\n",
       "      <td>32967.65</td>\n",
       "      <td>44114.064</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>314</th>\n",
       "      <td>002462.SZ</td>\n",
       "      <td>20220714</td>\n",
       "      <td>13.54</td>\n",
       "      <td>13.75</td>\n",
       "      <td>13.50</td>\n",
       "      <td>13.59</td>\n",
       "      <td>13.54</td>\n",
       "      <td>0.05</td>\n",
       "      <td>0.3693</td>\n",
       "      <td>21967.00</td>\n",
       "      <td>29851.164</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>315</th>\n",
       "      <td>002462.SZ</td>\n",
       "      <td>20220713</td>\n",
       "      <td>13.55</td>\n",
       "      <td>13.63</td>\n",
       "      <td>13.39</td>\n",
       "      <td>13.54</td>\n",
       "      <td>13.61</td>\n",
       "      <td>-0.07</td>\n",
       "      <td>-0.5143</td>\n",
       "      <td>22793.00</td>\n",
       "      <td>30714.624</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>316</th>\n",
       "      <td>002462.SZ</td>\n",
       "      <td>20220712</td>\n",
       "      <td>13.65</td>\n",
       "      <td>13.69</td>\n",
       "      <td>13.41</td>\n",
       "      <td>13.61</td>\n",
       "      <td>13.65</td>\n",
       "      <td>-0.04</td>\n",
       "      <td>-0.2930</td>\n",
       "      <td>29679.00</td>\n",
       "      <td>40146.310</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>317</th>\n",
       "      <td>002462.SZ</td>\n",
       "      <td>20220711</td>\n",
       "      <td>13.20</td>\n",
       "      <td>13.96</td>\n",
       "      <td>13.07</td>\n",
       "      <td>13.65</td>\n",
       "      <td>13.18</td>\n",
       "      <td>0.47</td>\n",
       "      <td>3.5660</td>\n",
       "      <td>62827.00</td>\n",
       "      <td>85869.110</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>318 rows × 11 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "       ts_code trade_date   open   high    low  close  pre_close  change  \\\n",
       "0    002462.SZ   20231031  14.75  14.90  14.59  14.70      14.75   -0.05   \n",
       "1    002462.SZ   20231030  13.88  14.89  13.88  14.75      13.93    0.82   \n",
       "2    002462.SZ   20231027  13.70  13.98  13.51  13.93      13.64    0.29   \n",
       "3    002462.SZ   20231026  13.49  13.68  13.40  13.64      13.62    0.02   \n",
       "4    002462.SZ   20231025  13.65  13.77  13.58  13.62      13.67   -0.05   \n",
       "..         ...        ...    ...    ...    ...    ...        ...     ...   \n",
       "313  002462.SZ   20220715  13.61  13.66  13.12  13.13      13.59   -0.46   \n",
       "314  002462.SZ   20220714  13.54  13.75  13.50  13.59      13.54    0.05   \n",
       "315  002462.SZ   20220713  13.55  13.63  13.39  13.54      13.61   -0.07   \n",
       "316  002462.SZ   20220712  13.65  13.69  13.41  13.61      13.65   -0.04   \n",
       "317  002462.SZ   20220711  13.20  13.96  13.07  13.65      13.18    0.47   \n",
       "\n",
       "     pct_chg        vol      amount  \n",
       "0    -0.3390   65859.96   96984.271  \n",
       "1     5.8866  123932.16  180119.372  \n",
       "2     2.1261   35782.00   49386.168  \n",
       "3     0.1468   19215.00   26005.866  \n",
       "4    -0.3658   18484.00   25274.163  \n",
       "..       ...        ...         ...  \n",
       "313  -3.3848   32967.65   44114.064  \n",
       "314   0.3693   21967.00   29851.164  \n",
       "315  -0.5143   22793.00   30714.624  \n",
       "316  -0.2930   29679.00   40146.310  \n",
       "317   3.5660   62827.00   85869.110  \n",
       "\n",
       "[318 rows x 11 columns]"
      ]
     },
     "execution_count": 71,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "hp"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 83,
   "id": "bb5b26fe-d2b4-4ee8-98c5-079086f5842f",
   "metadata": {},
   "outputs": [],
   "source": [
    "ts_codes=d1.select(\n",
    "    \"证券代码\",\n",
    "    证券代码2=(\n",
    "        pl.when(pl.col(\"证券代码\").str.head(1).is_in([\"0\",\"3\"]))\n",
    "        .then(pl.format(\"{}.SZ\",pl.col(\"证券代码\")))\n",
    "        .when(pl.col(\"证券代码\").str.head(1)==\"6\")\n",
    "        .then(pl.format(\"{}.SH\",pl.col(\"证券代码\")))\n",
    "    ),\n",
    ").to_series().unique().sort().to_list()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 84,
   "id": "8380ecba-2790-4cd8-bfb0-11bd6257d376",
   "metadata": {},
   "outputs": [],
   "source": [
    "from tqdm.notebook import tqdm"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 85,
   "id": "fd708b5c-208d-49d9-b50f-f5e9628e8bff",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "application/vnd.jupyter.widget-view+json": {
       "model_id": "7cc5b5466c3e4ddbb22c48218e267bb6",
       "version_major": 2,
       "version_minor": 0
      },
      "text/plain": [
       "  0%|          | 0/149 [00:00<?, ?it/s]"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "hq=[\n",
    "    pl.from_pandas(\n",
    "        pro.daily(\n",
    "            ts_code=ts_code,\n",
    "            start_date=format(start_date,\"%Y%m%d\"),\n",
    "            end_date=format(end_date,\"%Y%m%d\"),\n",
    "        )\n",
    "    )\n",
    "    for ts_code in tqdm(ts_codes)\n",
    "]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 86,
   "id": "c7877b54-0564-4191-baa6-7d4717ffcd70",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "149"
      ]
     },
     "execution_count": 86,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "len(hq)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 87,
   "id": "da3ff89e-38cb-4711-a593-444ab0fc1b58",
   "metadata": {},
   "outputs": [],
   "source": [
    "hq=pl.concat(hq)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 89,
   "id": "03ce96b9-78a6-46ce-822e-29c3530be9c7",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "/c/Users/86182/repo/week08\n"
     ]
    }
   ],
   "source": [
    "!pwd\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 90,
   "id": "1a130fe3-c509-4b52-b62a-2d2d03266340",
   "metadata": {},
   "outputs": [],
   "source": [
    "hq.write_parquet(\"daily.parquet\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 111,
   "id": "af856940-586a-4675-93bd-187d89649333",
   "metadata": {},
   "outputs": [],
   "source": [
    "hq=pl.read_parquet(\"daily.parquet\")\n",
    "hq=hq.with_columns(\n",
    "    pl.col(\"ts_code\").str.head(6),\n",
    "    pl.col(\"trade_date\").str.to_date(\"%Y%m%d\"),\n",
    ")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 112,
   "id": "a6a658a5-faef-4b70-a469-ac475084e5c4",
   "metadata": {
    "collapsed": true,
    "jupyter": {
     "outputs_hidden": true
    }
   },
   "outputs": [
    {
     "ename": "InvalidOperationError",
     "evalue": "arithmetic on string and numeric not allowed, try an explicit cast first\n\nResolved plan until failure:\n\n\t---> FAILED HERE RESOLVING 'sink' <---\n WITH_COLUMNS:\n [[([(col(\"成交数量\")) / (100.0)]) / (col(\"vol\"))].alias(\"vratio\")] \n  DF [\"序号\", \"券商\", \"交易日期\", \"交易时间\", ...]; PROJECT */27 COLUMNS",
     "output_type": "error",
     "traceback": [
      "\u001b[31m---------------------------------------------------------------------------\u001b[39m",
      "\u001b[31mInvalidOperationError\u001b[39m                     Traceback (most recent call last)",
      "\u001b[36mCell\u001b[39m\u001b[36m \u001b[39m\u001b[32mIn[112]\u001b[39m\u001b[32m, line 3\u001b[39m\n\u001b[32m      1\u001b[39m \u001b[43md1\u001b[49m\u001b[43m.\u001b[49m\u001b[43mjoin\u001b[49m\u001b[43m(\u001b[49m\n\u001b[32m      2\u001b[39m \u001b[43m    \u001b[49m\u001b[43mhq\u001b[49m\u001b[43m,\u001b[49m\u001b[43mleft_on\u001b[49m\u001b[43m=\u001b[49m\u001b[43m[\u001b[49m\u001b[33;43m\"\u001b[39;49m\u001b[33;43m交易日期\u001b[39;49m\u001b[33;43m\"\u001b[39;49m\u001b[43m,\u001b[49m\u001b[33;43m\"\u001b[39;49m\u001b[33;43m证券代码\u001b[39;49m\u001b[33;43m\"\u001b[39;49m\u001b[43m]\u001b[49m\u001b[43m,\u001b[49m\u001b[43mright_on\u001b[49m\u001b[43m=\u001b[49m\u001b[43m[\u001b[49m\u001b[33;43m\"\u001b[39;49m\u001b[33;43mtrade_date\u001b[39;49m\u001b[33;43m\"\u001b[39;49m\u001b[43m,\u001b[49m\u001b[33;43m\"\u001b[39;49m\u001b[33;43mts_code\u001b[39;49m\u001b[33;43m\"\u001b[39;49m\u001b[43m]\u001b[49m\u001b[43m,\u001b[49m\u001b[43mhow\u001b[49m\u001b[43m=\u001b[49m\u001b[33;43m\"\u001b[39;49m\u001b[33;43mleft\u001b[39;49m\u001b[33;43m\"\u001b[39;49m\n\u001b[32m----> \u001b[39m\u001b[32m3\u001b[39m \u001b[43m)\u001b[49m\u001b[43m.\u001b[49m\u001b[43mwith_columns\u001b[49m\u001b[43m(\u001b[49m\n\u001b[32m      4\u001b[39m \u001b[43m    \u001b[49m\u001b[43mvratio\u001b[49m\u001b[43m=\u001b[49m\u001b[43mpl\u001b[49m\u001b[43m.\u001b[49m\u001b[43mcol\u001b[49m\u001b[43m(\u001b[49m\u001b[33;43m\"\u001b[39;49m\u001b[33;43m成交数量\u001b[39;49m\u001b[33;43m\"\u001b[39;49m\u001b[43m)\u001b[49m\u001b[43m \u001b[49m\u001b[43m/\u001b[49m\u001b[43m \u001b[49m\u001b[32;43m100\u001b[39;49m\u001b[43m \u001b[49m\u001b[43m/\u001b[49m\u001b[43m \u001b[49m\u001b[43mpl\u001b[49m\u001b[43m.\u001b[49m\u001b[43mcol\u001b[49m\u001b[43m(\u001b[49m\u001b[33;43m\"\u001b[39;49m\u001b[33;43mvol\u001b[39;49m\u001b[33;43m\"\u001b[39;49m\u001b[43m)\u001b[49m\u001b[43m,\u001b[49m\n\u001b[32m      5\u001b[39m \u001b[43m)\u001b[49m.sort(\u001b[33m\"\u001b[39m\u001b[33mvratio\u001b[39m\u001b[33m\"\u001b[39m)\n",
      "\u001b[36mFile \u001b[39m\u001b[32mD:\\anana\\damangshe\\envs\\week07\\Lib\\site-packages\\polars\\dataframe\\frame.py:9806\u001b[39m, in \u001b[36mDataFrame.with_columns\u001b[39m\u001b[34m(self, *exprs, **named_exprs)\u001b[39m\n\u001b[32m   9660\u001b[39m \u001b[38;5;28;01mdef\u001b[39;00m\u001b[38;5;250m \u001b[39m\u001b[34mwith_columns\u001b[39m(\n\u001b[32m   9661\u001b[39m     \u001b[38;5;28mself\u001b[39m,\n\u001b[32m   9662\u001b[39m     *exprs: IntoExpr | Iterable[IntoExpr],\n\u001b[32m   9663\u001b[39m     **named_exprs: IntoExpr,\n\u001b[32m   9664\u001b[39m ) -> DataFrame:\n\u001b[32m   9665\u001b[39m \u001b[38;5;250m    \u001b[39m\u001b[33;03m\"\"\"\u001b[39;00m\n\u001b[32m   9666\u001b[39m \u001b[33;03m    Add columns to this DataFrame.\u001b[39;00m\n\u001b[32m   9667\u001b[39m \n\u001b[32m   (...)\u001b[39m\u001b[32m   9804\u001b[39m \u001b[33;03m    └─────┴──────┴─────────────┘\u001b[39;00m\n\u001b[32m   9805\u001b[39m \u001b[33;03m    \"\"\"\u001b[39;00m\n\u001b[32m-> \u001b[39m\u001b[32m9806\u001b[39m     \u001b[38;5;28;01mreturn\u001b[39;00m \u001b[38;5;28;43mself\u001b[39;49m\u001b[43m.\u001b[49m\u001b[43mlazy\u001b[49m\u001b[43m(\u001b[49m\u001b[43m)\u001b[49m\u001b[43m.\u001b[49m\u001b[43mwith_columns\u001b[49m\u001b[43m(\u001b[49m\u001b[43m*\u001b[49m\u001b[43mexprs\u001b[49m\u001b[43m,\u001b[49m\u001b[43m \u001b[49m\u001b[43m*\u001b[49m\u001b[43m*\u001b[49m\u001b[43mnamed_exprs\u001b[49m\u001b[43m)\u001b[49m\u001b[43m.\u001b[49m\u001b[43mcollect\u001b[49m\u001b[43m(\u001b[49m\u001b[43m_eager\u001b[49m\u001b[43m=\u001b[49m\u001b[38;5;28;43;01mTrue\u001b[39;49;00m\u001b[43m)\u001b[49m\n",
      "\u001b[36mFile \u001b[39m\u001b[32mD:\\anana\\damangshe\\envs\\week07\\Lib\\site-packages\\polars\\_utils\\deprecation.py:93\u001b[39m, in \u001b[36mdeprecate_streaming_parameter.<locals>.decorate.<locals>.wrapper\u001b[39m\u001b[34m(*args, **kwargs)\u001b[39m\n\u001b[32m     89\u001b[39m         kwargs[\u001b[33m\"\u001b[39m\u001b[33mengine\u001b[39m\u001b[33m\"\u001b[39m] = \u001b[33m\"\u001b[39m\u001b[33min-memory\u001b[39m\u001b[33m\"\u001b[39m\n\u001b[32m     91\u001b[39m     \u001b[38;5;28;01mdel\u001b[39;00m kwargs[\u001b[33m\"\u001b[39m\u001b[33mstreaming\u001b[39m\u001b[33m\"\u001b[39m]\n\u001b[32m---> \u001b[39m\u001b[32m93\u001b[39m \u001b[38;5;28;01mreturn\u001b[39;00m \u001b[43mfunction\u001b[49m\u001b[43m(\u001b[49m\u001b[43m*\u001b[49m\u001b[43margs\u001b[49m\u001b[43m,\u001b[49m\u001b[43m \u001b[49m\u001b[43m*\u001b[49m\u001b[43m*\u001b[49m\u001b[43mkwargs\u001b[49m\u001b[43m)\u001b[49m\n",
      "\u001b[36mFile \u001b[39m\u001b[32mD:\\anana\\damangshe\\envs\\week07\\Lib\\site-packages\\polars\\lazyframe\\frame.py:2206\u001b[39m, in \u001b[36mLazyFrame.collect\u001b[39m\u001b[34m(self, type_coercion, _type_check, predicate_pushdown, projection_pushdown, simplify_expression, slice_pushdown, comm_subplan_elim, comm_subexpr_elim, cluster_with_columns, collapse_joins, no_optimization, engine, background, _check_order, _eager, **_kwargs)\u001b[39m\n\u001b[32m   2204\u001b[39m \u001b[38;5;66;03m# Only for testing purposes\u001b[39;00m\n\u001b[32m   2205\u001b[39m callback = _kwargs.get(\u001b[33m\"\u001b[39m\u001b[33mpost_opt_callback\u001b[39m\u001b[33m\"\u001b[39m, callback)\n\u001b[32m-> \u001b[39m\u001b[32m2206\u001b[39m \u001b[38;5;28;01mreturn\u001b[39;00m wrap_df(\u001b[43mldf\u001b[49m\u001b[43m.\u001b[49m\u001b[43mcollect\u001b[49m\u001b[43m(\u001b[49m\u001b[43mengine\u001b[49m\u001b[43m,\u001b[49m\u001b[43m \u001b[49m\u001b[43mcallback\u001b[49m\u001b[43m)\u001b[49m)\n",
      "\u001b[31mInvalidOperationError\u001b[39m: arithmetic on string and numeric not allowed, try an explicit cast first\n\nResolved plan until failure:\n\n\t---> FAILED HERE RESOLVING 'sink' <---\n WITH_COLUMNS:\n [[([(col(\"成交数量\")) / (100.0)]) / (col(\"vol\"))].alias(\"vratio\")] \n  DF [\"序号\", \"券商\", \"交易日期\", \"交易时间\", ...]; PROJECT */27 COLUMNS"
     ]
    }
   ],
   "source": [
    "d1.join(\n",
    "    hq,left_on=[\"交易日期\",\"证券代码\"],right_on=[\"trade_date\",\"ts_code\"],how=\"left\"\n",
    ").with_columns(\n",
    "    vratio=pl.col(\"成交数量\") / 100 / pl.col(\"vol\"),\n",
    ").sort(\"vratio\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 110,
   "id": "665474d4-60bb-4b9f-a693-7e76adfca313",
   "metadata": {
    "collapsed": true,
    "jupyter": {
     "outputs_hidden": true
    }
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div><style>\n",
       ".dataframe > thead > tr,\n",
       ".dataframe > tbody > tr {\n",
       "  text-align: right;\n",
       "  white-space: pre-wrap;\n",
       "}\n",
       "</style>\n",
       "<small>shape: (71_237, 19)</small><table border=\"1\" class=\"dataframe\"><thead><tr><th>日期</th><th>证券代码</th><th>序号</th><th>券商</th><th>交易时间</th><th>证券名称</th><th>买卖标志</th><th>成交价格</th><th>成交数量</th><th>成交金额</th><th>手续费</th><th>印花税</th><th>过户费</th><th>其他费</th><th>发生金额</th><th>手续费率</th><th>印花税率</th><th>过户费率</th><th>结余数量</th></tr><tr><td>date</td><td>str</td><td>u32</td><td>str</td><td>str</td><td>str</td><td>str</td><td>f64</td><td>f64</td><td>f64</td><td>f64</td><td>f64</td><td>f64</td><td>f64</td><td>f64</td><td>f64</td><td>f64</td><td>f64</td><td>f64</td></tr></thead><tbody><tr><td>2022-07-11</td><td>&quot;000096&quot;</td><td>null</td><td>null</td><td>null</td><td>null</td><td>null</td><td>null</td><td>null</td><td>null</td><td>null</td><td>null</td><td>null</td><td>null</td><td>null</td><td>null</td><td>null</td><td>null</td><td>0.0</td></tr><tr><td>2022-07-11</td><td>&quot;000532&quot;</td><td>null</td><td>null</td><td>null</td><td>null</td><td>null</td><td>null</td><td>null</td><td>null</td><td>null</td><td>null</td><td>null</td><td>null</td><td>null</td><td>null</td><td>null</td><td>null</td><td>0.0</td></tr><tr><td>2022-07-11</td><td>&quot;000559&quot;</td><td>null</td><td>null</td><td>null</td><td>null</td><td>null</td><td>null</td><td>null</td><td>null</td><td>null</td><td>null</td><td>null</td><td>null</td><td>null</td><td>null</td><td>null</td><td>null</td><td>0.0</td></tr><tr><td>2022-07-11</td><td>&quot;000599&quot;</td><td>null</td><td>null</td><td>null</td><td>null</td><td>null</td><td>null</td><td>null</td><td>null</td><td>null</td><td>null</td><td>null</td><td>null</td><td>null</td><td>null</td><td>null</td><td>null</td><td>0.0</td></tr><tr><td>2022-07-11</td><td>&quot;000655&quot;</td><td>null</td><td>null</td><td>null</td><td>null</td><td>null</td><td>null</td><td>null</td><td>null</td><td>null</td><td>null</td><td>null</td><td>null</td><td>null</td><td>null</td><td>null</td><td>null</td><td>0.0</td></tr><tr><td>&hellip;</td><td>&hellip;</td><td>&hellip;</td><td>&hellip;</td><td>&hellip;</td><td>&hellip;</td><td>&hellip;</td><td>&hellip;</td><td>&hellip;</td><td>&hellip;</td><td>&hellip;</td><td>&hellip;</td><td>&hellip;</td><td>&hellip;</td><td>&hellip;</td><td>&hellip;</td><td>&hellip;</td><td>&hellip;</td><td>&hellip;</td></tr><tr><td>2023-10-31</td><td>&quot;688299&quot;</td><td>null</td><td>null</td><td>null</td><td>null</td><td>null</td><td>null</td><td>null</td><td>null</td><td>null</td><td>null</td><td>null</td><td>null</td><td>null</td><td>null</td><td>null</td><td>null</td><td>0.0</td></tr><tr><td>2023-10-31</td><td>&quot;688321&quot;</td><td>null</td><td>null</td><td>null</td><td>null</td><td>null</td><td>null</td><td>null</td><td>null</td><td>null</td><td>null</td><td>null</td><td>null</td><td>null</td><td>null</td><td>null</td><td>null</td><td>0.0</td></tr><tr><td>2023-10-31</td><td>&quot;688360&quot;</td><td>null</td><td>null</td><td>null</td><td>null</td><td>null</td><td>null</td><td>null</td><td>null</td><td>null</td><td>null</td><td>null</td><td>null</td><td>null</td><td>null</td><td>null</td><td>null</td><td>0.0</td></tr><tr><td>2023-10-31</td><td>&quot;688393&quot;</td><td>null</td><td>null</td><td>null</td><td>null</td><td>null</td><td>null</td><td>null</td><td>null</td><td>null</td><td>null</td><td>null</td><td>null</td><td>null</td><td>null</td><td>null</td><td>null</td><td>0.0</td></tr><tr><td>2023-10-31</td><td>&quot;688660&quot;</td><td>null</td><td>null</td><td>null</td><td>null</td><td>null</td><td>null</td><td>null</td><td>null</td><td>null</td><td>null</td><td>null</td><td>null</td><td>null</td><td>null</td><td>null</td><td>null</td><td>20200.0</td></tr></tbody></table></div>"
      ],
      "text/plain": [
       "shape: (71_237, 19)\n",
       "┌──────────┬─────────┬──────┬──────┬───┬─────────┬─────────┬─────────┬─────────┐\n",
       "│ 日期     ┆ 证券代  ┆ 序号 ┆ 券商 ┆ … ┆ 手续费  ┆ 印花税  ┆ 过户费  ┆ 结余数  │\n",
       "│ ---      ┆ 码      ┆ ---  ┆ ---  ┆   ┆ 率      ┆ 率      ┆ 率      ┆ 量      │\n",
       "│ date     ┆ ---     ┆ u32  ┆ str  ┆   ┆ ---     ┆ ---     ┆ ---     ┆ ---     │\n",
       "│          ┆ str     ┆      ┆      ┆   ┆ f64     ┆ f64     ┆ f64     ┆ f64     │\n",
       "╞══════════╪═════════╪══════╪══════╪═══╪═════════╪═════════╪═════════╪═════════╡\n",
       "│ 2022-07- ┆ 000096  ┆ null ┆ null ┆ … ┆ null    ┆ null    ┆ null    ┆ 0.0     │\n",
       "│ 11       ┆         ┆      ┆      ┆   ┆         ┆         ┆         ┆         │\n",
       "│ 2022-07- ┆ 000532  ┆ null ┆ null ┆ … ┆ null    ┆ null    ┆ null    ┆ 0.0     │\n",
       "│ 11       ┆         ┆      ┆      ┆   ┆         ┆         ┆         ┆         │\n",
       "│ 2022-07- ┆ 000559  ┆ null ┆ null ┆ … ┆ null    ┆ null    ┆ null    ┆ 0.0     │\n",
       "│ 11       ┆         ┆      ┆      ┆   ┆         ┆         ┆         ┆         │\n",
       "│ 2022-07- ┆ 000599  ┆ null ┆ null ┆ … ┆ null    ┆ null    ┆ null    ┆ 0.0     │\n",
       "│ 11       ┆         ┆      ┆      ┆   ┆         ┆         ┆         ┆         │\n",
       "│ 2022-07- ┆ 000655  ┆ null ┆ null ┆ … ┆ null    ┆ null    ┆ null    ┆ 0.0     │\n",
       "│ 11       ┆         ┆      ┆      ┆   ┆         ┆         ┆         ┆         │\n",
       "│ …        ┆ …       ┆ …    ┆ …    ┆ … ┆ …       ┆ …       ┆ …       ┆ …       │\n",
       "│ 2023-10- ┆ 688299  ┆ null ┆ null ┆ … ┆ null    ┆ null    ┆ null    ┆ 0.0     │\n",
       "│ 31       ┆         ┆      ┆      ┆   ┆         ┆         ┆         ┆         │\n",
       "│ 2023-10- ┆ 688321  ┆ null ┆ null ┆ … ┆ null    ┆ null    ┆ null    ┆ 0.0     │\n",
       "│ 31       ┆         ┆      ┆      ┆   ┆         ┆         ┆         ┆         │\n",
       "│ 2023-10- ┆ 688360  ┆ null ┆ null ┆ … ┆ null    ┆ null    ┆ null    ┆ 0.0     │\n",
       "│ 31       ┆         ┆      ┆      ┆   ┆         ┆         ┆         ┆         │\n",
       "│ 2023-10- ┆ 688393  ┆ null ┆ null ┆ … ┆ null    ┆ null    ┆ null    ┆ 0.0     │\n",
       "│ 31       ┆         ┆      ┆      ┆   ┆         ┆         ┆         ┆         │\n",
       "│ 2023-10- ┆ 688660  ┆ null ┆ null ┆ … ┆ null    ┆ null    ┆ null    ┆ 20200.0 │\n",
       "│ 31       ┆         ┆      ┆      ┆   ┆         ┆         ┆         ┆         │\n",
       "└──────────┴─────────┴──────┴──────┴───┴─────────┴─────────┴─────────┴─────────┘"
      ]
     },
     "execution_count": 110,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "d3=k.join(d1,left_on=[\"日期\",\"证券代码\"],right_on=[\"交易日期\",\"证券代码\"],how=\"left\").sort(\"日期\",\"证券代码\").with_columns(\n",
    "    结余数量=(pl.when(pl.col(\"买卖标志\")==\"买入\").then(pl.col(\"成交数量\")).when(pl.col(\"买卖标志\")==\"卖出\").then(-pl.col(\"成交数量\")).otherwise(0)\n",
    "         .cum_sum().over(\"证券代码\")\n",
    "         ),\n",
    ")\n",
    "d3"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 114,
   "id": "59daf22a-5019-4ef2-86f3-86ce5870ba31",
   "metadata": {
    "collapsed": true,
    "jupyter": {
     "outputs_hidden": true
    }
   },
   "outputs": [
    {
     "ename": "NameError",
     "evalue": "name 'p1' is not defined",
     "output_type": "error",
     "traceback": [
      "\u001b[31m---------------------------------------------------------------------------\u001b[39m",
      "\u001b[31mNameError\u001b[39m                                 Traceback (most recent call last)",
      "\u001b[36mCell\u001b[39m\u001b[36m \u001b[39m\u001b[32mIn[114]\u001b[39m\u001b[32m, line 1\u001b[39m\n\u001b[32m----> \u001b[39m\u001b[32m1\u001b[39m \u001b[43mp1\u001b[49m.DateFrame({\u001b[33m\"\u001b[39m\u001b[33ma\u001b[39m\u001b[33m\"\u001b[39m:[\u001b[32m2\u001b[39m,\u001b[32m8\u001b[39m,\u001b[38;5;28;01mNone\u001b[39;00m,\u001b[38;5;28;01mNone\u001b[39;00m,\u001b[32m1\u001b[39m,\u001b[38;5;28;01mNone\u001b[39;00m,\u001b[32m9\u001b[39m,\u001b[38;5;28;01mNone\u001b[39;00m]}).with_columns(\n\u001b[32m      2\u001b[39m     b=pl.col.a.fill_null(strategy=\u001b[33m\"\u001b[39m\u001b[33mbackward\u001b[39m\u001b[33m\"\u001b[39m)\n\u001b[32m      3\u001b[39m )\n",
      "\u001b[31mNameError\u001b[39m: name 'p1' is not defined"
     ]
    }
   ],
   "source": [
    "p1.DateFrame({\"a\":[2,8,None,None,1,None,9,None]}).with_columns(\n",
    "    b=pl.col.a.fill_null(strategy=\"backward\")\n",
    ")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 116,
   "id": "005d8e9b-df8b-4e66-b559-c4707f061470",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div><style>\n",
       ".dataframe > thead > tr,\n",
       ".dataframe > tbody > tr {\n",
       "  text-align: right;\n",
       "  white-space: pre-wrap;\n",
       "}\n",
       "</style>\n",
       "<small>shape: (71_237, 28)</small><table border=\"1\" class=\"dataframe\"><thead><tr><th>日期</th><th>证券代码</th><th>序号</th><th>券商</th><th>交易时间</th><th>证券名称</th><th>买卖标志</th><th>成交价格</th><th>成交数量</th><th>成交金额</th><th>手续费</th><th>印花税</th><th>过户费</th><th>其他费</th><th>发生金额</th><th>手续费率</th><th>印花税率</th><th>过户费率</th><th>结余数量</th><th>open</th><th>high</th><th>low</th><th>close</th><th>pre_close</th><th>change</th><th>pct_chg</th><th>vol</th><th>amount</th></tr><tr><td>date</td><td>str</td><td>u32</td><td>str</td><td>str</td><td>str</td><td>str</td><td>f64</td><td>f64</td><td>f64</td><td>f64</td><td>f64</td><td>f64</td><td>f64</td><td>f64</td><td>f64</td><td>f64</td><td>f64</td><td>f64</td><td>str</td><td>str</td><td>str</td><td>str</td><td>str</td><td>str</td><td>str</td><td>str</td><td>str</td></tr></thead><tbody><tr><td>2022-07-11</td><td>&quot;000096&quot;</td><td>null</td><td>null</td><td>null</td><td>null</td><td>null</td><td>null</td><td>null</td><td>null</td><td>null</td><td>null</td><td>null</td><td>null</td><td>null</td><td>null</td><td>null</td><td>null</td><td>0.0</td><td>null</td><td>null</td><td>null</td><td>null</td><td>null</td><td>null</td><td>null</td><td>null</td><td>null</td></tr><tr><td>2022-07-11</td><td>&quot;000532&quot;</td><td>null</td><td>null</td><td>null</td><td>null</td><td>null</td><td>null</td><td>null</td><td>null</td><td>null</td><td>null</td><td>null</td><td>null</td><td>null</td><td>null</td><td>null</td><td>null</td><td>0.0</td><td>null</td><td>null</td><td>null</td><td>null</td><td>null</td><td>null</td><td>null</td><td>null</td><td>null</td></tr><tr><td>2022-07-11</td><td>&quot;000559&quot;</td><td>null</td><td>null</td><td>null</td><td>null</td><td>null</td><td>null</td><td>null</td><td>null</td><td>null</td><td>null</td><td>null</td><td>null</td><td>null</td><td>null</td><td>null</td><td>null</td><td>0.0</td><td>null</td><td>null</td><td>null</td><td>null</td><td>null</td><td>null</td><td>null</td><td>null</td><td>null</td></tr><tr><td>2022-07-11</td><td>&quot;000599&quot;</td><td>null</td><td>null</td><td>null</td><td>null</td><td>null</td><td>null</td><td>null</td><td>null</td><td>null</td><td>null</td><td>null</td><td>null</td><td>null</td><td>null</td><td>null</td><td>null</td><td>0.0</td><td>null</td><td>null</td><td>null</td><td>null</td><td>null</td><td>null</td><td>null</td><td>null</td><td>null</td></tr><tr><td>2022-07-11</td><td>&quot;000655&quot;</td><td>null</td><td>null</td><td>null</td><td>null</td><td>null</td><td>null</td><td>null</td><td>null</td><td>null</td><td>null</td><td>null</td><td>null</td><td>null</td><td>null</td><td>null</td><td>null</td><td>0.0</td><td>null</td><td>null</td><td>null</td><td>null</td><td>null</td><td>null</td><td>null</td><td>null</td><td>null</td></tr><tr><td>&hellip;</td><td>&hellip;</td><td>&hellip;</td><td>&hellip;</td><td>&hellip;</td><td>&hellip;</td><td>&hellip;</td><td>&hellip;</td><td>&hellip;</td><td>&hellip;</td><td>&hellip;</td><td>&hellip;</td><td>&hellip;</td><td>&hellip;</td><td>&hellip;</td><td>&hellip;</td><td>&hellip;</td><td>&hellip;</td><td>&hellip;</td><td>&hellip;</td><td>&hellip;</td><td>&hellip;</td><td>&hellip;</td><td>&hellip;</td><td>&hellip;</td><td>&hellip;</td><td>&hellip;</td><td>&hellip;</td></tr><tr><td>2023-10-31</td><td>&quot;688299&quot;</td><td>null</td><td>null</td><td>null</td><td>null</td><td>null</td><td>null</td><td>null</td><td>null</td><td>null</td><td>null</td><td>null</td><td>null</td><td>null</td><td>null</td><td>null</td><td>null</td><td>0.0</td><td>null</td><td>null</td><td>null</td><td>null</td><td>null</td><td>null</td><td>null</td><td>null</td><td>null</td></tr><tr><td>2023-10-31</td><td>&quot;688321&quot;</td><td>null</td><td>null</td><td>null</td><td>null</td><td>null</td><td>null</td><td>null</td><td>null</td><td>null</td><td>null</td><td>null</td><td>null</td><td>null</td><td>null</td><td>null</td><td>null</td><td>0.0</td><td>null</td><td>null</td><td>null</td><td>null</td><td>null</td><td>null</td><td>null</td><td>null</td><td>null</td></tr><tr><td>2023-10-31</td><td>&quot;688360&quot;</td><td>null</td><td>null</td><td>null</td><td>null</td><td>null</td><td>null</td><td>null</td><td>null</td><td>null</td><td>null</td><td>null</td><td>null</td><td>null</td><td>null</td><td>null</td><td>null</td><td>0.0</td><td>null</td><td>null</td><td>null</td><td>null</td><td>null</td><td>null</td><td>null</td><td>null</td><td>null</td></tr><tr><td>2023-10-31</td><td>&quot;688393&quot;</td><td>null</td><td>null</td><td>null</td><td>null</td><td>null</td><td>null</td><td>null</td><td>null</td><td>null</td><td>null</td><td>null</td><td>null</td><td>null</td><td>null</td><td>null</td><td>null</td><td>0.0</td><td>null</td><td>null</td><td>null</td><td>null</td><td>null</td><td>null</td><td>null</td><td>null</td><td>null</td></tr><tr><td>2023-10-31</td><td>&quot;688660&quot;</td><td>null</td><td>null</td><td>null</td><td>null</td><td>null</td><td>null</td><td>null</td><td>null</td><td>null</td><td>null</td><td>null</td><td>null</td><td>null</td><td>null</td><td>null</td><td>null</td><td>20200.0</td><td>null</td><td>null</td><td>null</td><td>null</td><td>null</td><td>null</td><td>null</td><td>null</td><td>null</td></tr></tbody></table></div>"
      ],
      "text/plain": [
       "shape: (71_237, 28)\n",
       "┌────────────┬──────────┬──────┬──────┬───┬────────┬─────────┬──────┬────────┐\n",
       "│ 日期       ┆ 证券代码 ┆ 序号 ┆ 券商 ┆ … ┆ change ┆ pct_chg ┆ vol  ┆ amount │\n",
       "│ ---        ┆ ---      ┆ ---  ┆ ---  ┆   ┆ ---    ┆ ---     ┆ ---  ┆ ---    │\n",
       "│ date       ┆ str      ┆ u32  ┆ str  ┆   ┆ str    ┆ str     ┆ str  ┆ str    │\n",
       "╞════════════╪══════════╪══════╪══════╪═══╪════════╪═════════╪══════╪════════╡\n",
       "│ 2022-07-11 ┆ 000096   ┆ null ┆ null ┆ … ┆ null   ┆ null    ┆ null ┆ null   │\n",
       "│ 2022-07-11 ┆ 000532   ┆ null ┆ null ┆ … ┆ null   ┆ null    ┆ null ┆ null   │\n",
       "│ 2022-07-11 ┆ 000559   ┆ null ┆ null ┆ … ┆ null   ┆ null    ┆ null ┆ null   │\n",
       "│ 2022-07-11 ┆ 000599   ┆ null ┆ null ┆ … ┆ null   ┆ null    ┆ null ┆ null   │\n",
       "│ 2022-07-11 ┆ 000655   ┆ null ┆ null ┆ … ┆ null   ┆ null    ┆ null ┆ null   │\n",
       "│ …          ┆ …        ┆ …    ┆ …    ┆ … ┆ …      ┆ …       ┆ …    ┆ …      │\n",
       "│ 2023-10-31 ┆ 688299   ┆ null ┆ null ┆ … ┆ null   ┆ null    ┆ null ┆ null   │\n",
       "│ 2023-10-31 ┆ 688321   ┆ null ┆ null ┆ … ┆ null   ┆ null    ┆ null ┆ null   │\n",
       "│ 2023-10-31 ┆ 688360   ┆ null ┆ null ┆ … ┆ null   ┆ null    ┆ null ┆ null   │\n",
       "│ 2023-10-31 ┆ 688393   ┆ null ┆ null ┆ … ┆ null   ┆ null    ┆ null ┆ null   │\n",
       "│ 2023-10-31 ┆ 688660   ┆ null ┆ null ┆ … ┆ null   ┆ null    ┆ null ┆ null   │\n",
       "└────────────┴──────────┴──────┴──────┴───┴────────┴─────────┴──────┴────────┘"
      ]
     },
     "execution_count": 116,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "d3.join(hq,left_on=[\"日期\",\"证券代码\"],right_on=[\"trade_date\",\"ts_code\"],how=\"left\",)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "0d24b813-26ad-48ec-a44e-2f4abe858c3c",
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3 (ipykernel)",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.12.10"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}
